import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
//some comment
/**
 * @author edhem.brakmic
 * ddfdffgggrfrffg
 */

public class DbConnector {

	private Connection connection = null;

	public DbConnector() {

		System.out.println("-------- PostgreSQL "
				+ "JDBC Connection Testing ------------");

		try {

			Class.forName("org.postgresql.Driver");

		} catch (ClassNotFoundException e) {

			System.out.println("Where is your PostgreSQL JDBC Driver? "
					+ "Include in your library path!");
			e.printStackTrace();
			return;

		}

		System.out.println("PostgreSQL JDBC Driver Registered!");

		try {

			connection = DriverManager.getConnection(
					"jdbc:postgresql://localhost:5432/wsn", "postgres",
					"wireless");

		} catch (SQLException e) {

			System.out.println("Connection Failed! Check output console");
			e.printStackTrace();
			return;

		}

		if (connection != null) {
			System.out.println("You made it, take control your database now!");
		} else {
			System.out.println("Failed to make connection!");
		}
	}

	public void CreateTable(String tableName, Object[] fields, Object[] types)
			throws SQLException {

		// Get a statement from the connection
		Statement stmt = connection.createStatement();

		// Execute the query
		String stm = "CREATE TABLE "
				+ tableName
				+ " (id INT UNIQUE, timestamp_m timestamp, dest_addr int, link_source_addr int, "
				+ "msg_len smallint, groupID smallint, handlerID smallint, ";
		for (int i = 0; i < types.length; i++) {
			stm += fields[i] + " " + types[i];
			if (i + 1 < types.length) {
				stm += ",";
			}

		}
		stm += ");";
		System.out.println(stm);
		String init = "CREATE SEQUENCE " + tableName + "_id_seq;"
				+ "ALTER TABLE " + tableName
				+ " ALTER COLUMN id SET DEFAULT NEXTVAL('" + tableName
				+ "_id_seq');" + "UPDATE " + tableName + " SET id = NEXTVAL('"
				+ tableName + "_id_seq');";
		System.out.println(init);
		stmt.execute(stm + init);

	}

	public void insertValue(int destadd, int srcadd, int messlen, int grpid,
			int hndlid, int srcId, float value, float weight, float est) throws SQLException {

		// Get a statement from the connection
		Statement stmt = connection.createStatement();
		Date d = new Date();
		// Execute the query
		String stm = "INSERT INTO wsntest (timestamp_m,dest_addr, link_source_addr, msg_len, groupid, handlerid, "
				+ "mid, counter, weight, est) VALUES ('"
				+ new Timestamp(d.getTime()).toString()
				+ "',"
				+ destadd
				+ ", "
				+ srcadd
				+ ", "
				+ messlen
				+ ", "
				+ grpid
				+ ", "
				+ hndlid
				+ ", "
				+ srcId + ", " + value + ", " + weight+ ", " + est;

		stm += ");";
		System.out.println(stm);
		stmt.execute(stm);

	}

	@SuppressWarnings("unchecked")
	public List<String> getTableNames() throws SQLException {

		List<String> tables = new ArrayList();
		DatabaseMetaData meta = connection.getMetaData();
		ResultSet res = meta.getTables(null, null, null,
				new String[] { "TABLE" });
		while (res.next()) {

			tables.add(res.getString("TABLE_NAME"));

		}
		res.close();

		return tables;

	}

	@SuppressWarnings("unchecked")
	public TableM getTableData(String table) throws SQLException {

		TableM data = new TableM();

		List<String> columnNames = new ArrayList();
		List<String> columnTypes = new ArrayList();

		DatabaseMetaData meta = connection.getMetaData();
		ResultSet resCol = meta.getColumns(null, null, table, null);
		int numCol = 0;
		while (resCol.next()) {

			columnNames.add(resCol.getString("COLUMN_NAME"));
			columnTypes.add(resCol.getString("TYPE_NAME"));

			numCol = columnNames.size();

		}
		data.setColumnNames(columnNames.toArray());
		data.setColumnTypes(columnTypes.toArray());

		resCol.close();
		 System.out.println(table);
		Statement stmt = connection.createStatement();
		ResultSet resRows=null;
//		ResultSet resRows = stmt.executeQuery("SELECT COUNT(*) FROM " + table
//				+ ";");
//		resRows.next();
//		Object[][] dataPre = new Object[Integer.parseInt(resRows.getString(1))][numCol];
		Object[][] dataPre = new Object[1000][numCol];
		resRows = stmt.executeQuery("select * from " + table + " ORDER BY "
				+ table + ".timestamp_m DESC LIMIT 1000");

		int rowCounter = 0;
		while (resRows.next()) {

			for (int i = 0; i < numCol; i++) {
				dataPre[rowCounter][i] = resRows.getString(i + 1);
			}
			rowCounter++;
		}
		data.setRowData(dataPre);
		resRows.close();
		return data;

	}

	public void dropTable(String tableName) throws SQLException {
		// DROP TABLE tableName;
		// Get a statement from the connection
		Statement stmt = connection.createStatement();
		Date d = new Date();
		// Execute the query
		String stm = "DROP TABLE " + tableName + ";";
		stm += "DROP SEQUENCE " + tableName + "_id_seq;";
		// System.out.println(stm);
		stmt.execute(stm);

	}

}
